package com.tj.admin.db.dao.impl;
import com.fqgj.common.base.AbstractBaseMapper;
import com.fqgj.common.datasource.DataSource;
import com.tj.admin.db.dao.BizStatsSqlDao;
import com.tj.admin.db.dao.MsgCommonVo;
import com.tj.admin.db.entity.BizStatsSqlEntity;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
* User: anonymous
* Date: 2021-11-13
* Time: 19:25:36.593
*/

@Repository
public class BizStatsSqlDaoImpl extends AbstractBaseMapper<BizStatsSqlEntity> implements BizStatsSqlDao {


    @Override
    public List<MsgCommonVo> getAllAppCodes() {
        return getSqlSession().selectList(getStatement("getAllAppCodes"));
    }

    @Override
    public void createPayMerchant(String merchantCode, String productCategory) {
        Map<String, Object> params = new HashMap<>();
        params.put("merchantCode", merchantCode);
        params.put("productCategory", productCategory);
        List<String> sqls = new ArrayList<>();
        sqls.add("INSERT INTO `cashmama-pay`.p_merchant (`code`, `name`, `secret`, `state`, `deleted`)\n" +
                "        VALUES\n" +
                "        (#{merchantCode,jdbcType=VARCHAR}, #{merchantCode,jdbcType=VARCHAR}, '', 0, 0);");

        sqls.add("INSERT INTO `cashmama-pay`.p_merchant_product_category ( `code`, `name`, `merchant_code`, `state`, `deleted`, `gmt_modified`, `gmt_create`)\n" +
                "VALUES\n" +
                "\t(#{productCategory,jdbcType=VARCHAR}, #{productCategory,jdbcType=VARCHAR}, #{merchantCode,jdbcType=VARCHAR}, 0, 0, '2019-09-11 07:26:40', '2019-09-11 07:26:40');\n");
        sqls.forEach(sql -> {
            params.put("sql", sql);
            getSqlSession().selectList(getStatement("executeStatsSql"), params);
        });
    }

    @Override
    public void createPayAccount(String merchantCode, String productCategory, int paymentFundCode, int repaymentFundCode) {
        String sql1 = "INSERT INTO `cashmama-pay`.`p_fund_account`(`merchant_code`, `account_name`, `account_no`, `weight`, `fund_code`, `fund_status`, `control_type`, `fund_type`, `nbfc`, `nbfc_company_name`, `total_balance`, `available_balance`, `frozen_balance`, `accumulative_amount`, `bank_account_no`, `ifsc_code`, `bank_name`, `bank_account_name`, `bank_reserved_email`, `bank_bind_mobile`, `bank_reserved_address`, `version`, `gmt_create`, `gmt_modified`, `deleted`) VALUES (#{merchantCode,jdbcType=VARCHAR}, CONCAT(#{merchantCode,jdbcType=VARCHAR},'-ZAVRON'), '', 1, #{paymentFundCode,jdbcType=INTEGER}, 0, 0, 4, 'ZAVRON', 'ZAVRON FINANCE PRIVATE LIMITED', 0.00, 0.00, 0.00, 0.00, '', '', '', '', '', '', '', 0, '2019-09-23 16:12:34', '2019-09-27 14:27:20', 0)";
        Map<String, Object> params1 = new HashMap<>();
        params1.put("sql", sql1);
        params1.put("merchantCode", merchantCode);
        params1.put("productCategory", productCategory);
        params1.put("paymentFundCode", paymentFundCode);
        getSqlSession().selectList(getStatement("executeStatsSql"), params1);

        String sql2 = "INSERT INTO `cashmama-pay`.`p_fund_account`(`merchant_code`, `account_name`, `account_no`, `weight`, `fund_code`, `fund_status`, `control_type`, `fund_type`, `nbfc`, `nbfc_company_name`, `total_balance`, `available_balance`, `frozen_balance`, `accumulative_amount`, `bank_account_no`, `ifsc_code`, `bank_name`, `bank_account_name`, `bank_reserved_email`, `bank_bind_mobile`, `bank_reserved_address`, `version`, `gmt_create`, `gmt_modified`, `deleted`) VALUES (#{merchantCode,jdbcType=VARCHAR}, CONCAT(#{merchantCode,jdbcType=VARCHAR},'-REPAYMENT'), '', 1, #{repaymentFundCode,jdbcType=INTEGER}, 0, 0, 5, '', '', 0.00, 0.00, 0.00, 0.00, '', '', '', '', '', '', '', 0, '2019-09-16 19:01:57', '2019-09-27 13:02:19', 0)";
        Map<String, Object> params2 = new HashMap<>();
        params2.put("sql", sql2);
        params2.put("merchantCode", merchantCode);
        params2.put("productCategory", productCategory);
        params2.put("repaymentFundCode", repaymentFundCode);
        getSqlSession().selectList(getStatement("executeStatsSql"), params2);

        String sql3 = "INSERT INTO `cashmama-pay`.`p_fund_side_channel_map`( `fund_code`, `payment_channel_code`, `gmt_create`, `gmt_modified`, `deleted`) VALUES ( #{paymentFundCode,jdbcType=INTEGER}, 2, '2019-09-23 16:31:32', '2019-09-23 16:31:32', 0)";
        Map<String, Object> params3 = new HashMap<>();
        params3.put("sql", sql3);
        params3.put("paymentFundCode", paymentFundCode);
        getSqlSession().selectList(getStatement("executeStatsSql"), params3);

        Map<String, Object> params4 = new HashMap<>();
        params4.put("merchantCode", merchantCode);
        params4.put("productCategory", productCategory);
        List<String> sqls = new ArrayList<>();
        sqls.add("INSERT INTO `cashmama-pay`.`p_product_category_channel`(`product_category_code`, `merchant_code`, `channel_code`, `weight`, `channel_service_fee`, `channel_service_fee_rate`, `config`, `state`, `deleted`, `gmt_modified`, `gmt_create`) VALUES (#{productCategory,jdbcType=VARCHAR}, #{merchantCode,jdbcType=VARCHAR}, -2, 1, 0.0000, 0.0000, '', 0, 0, '2019-09-23 16:34:24', '2019-09-23 16:33:31')");
        sqls.add("INSERT INTO `cashmama-pay`.`p_product_category_channel`(`product_category_code`, `merchant_code`, `channel_code`, `weight`, `channel_service_fee`, `channel_service_fee_rate`, `config`, `state`, `deleted`, `gmt_modified`, `gmt_create`) VALUES (#{productCategory,jdbcType=VARCHAR}, #{merchantCode,jdbcType=VARCHAR}, 1, 1, 0.0000, 0.0230, '{}', 0, 0, '2019-09-24 12:32:53', '2019-09-23 16:34:49')");
        sqls.add("INSERT INTO `cashmama-pay`.`p_product_category_channel`(`product_category_code`, `merchant_code`, `channel_code`, `weight`, `channel_service_fee`, `channel_service_fee_rate`, `config`, `state`, `deleted`, `gmt_modified`, `gmt_create`) VALUES (#{productCategory,jdbcType=VARCHAR}, #{merchantCode,jdbcType=VARCHAR}, 2, 1, 0.0000, 0.0000, '{}', 0, 0, '2019-09-24 12:33:17', '2019-09-23 16:35:13')");
        sqls.forEach(sql -> {
            params4.put("sql", sql);
            getSqlSession().selectList(getStatement("executeStatsSql"), params4);
        });

    }

    @Override
    public void createClStage(String merchantCode) {
        Map<String, Object> params = new HashMap<>();
        params.put("merchantCode", merchantCode);
        List<String> sqls = new ArrayList<>();
        sqls.add("INSERT INTO `cashmama-admin-rest`.`cl_stage`(`stage_code`, `stage_name`, `min_overdue_days`, `max_overdue_days`, `merchant_code`, `deleted`, `gmt_modified`, `gmt_create`) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_D0'), 'D0', 0, 0, #{merchantCode,jdbcType=VARCHAR}, 0, '2019-09-05 20:43:43', '2019-09-02 20:13:12')");
        sqls.add("INSERT INTO `cashmama-admin-rest`.`cl_stage`(`stage_code`, `stage_name`, `min_overdue_days`, `max_overdue_days`, `merchant_code`, `deleted`, `gmt_modified`, `gmt_create`) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_S1'), 'S1', 1, 7, #{merchantCode,jdbcType=VARCHAR}, 0, '2019-09-06 11:13:38', '2019-09-02 20:13:12')");
        sqls.add("INSERT INTO `cashmama-admin-rest`.`cl_stage`(`stage_code`, `stage_name`, `min_overdue_days`, `max_overdue_days`, `merchant_code`, `deleted`, `gmt_modified`, `gmt_create`) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_S2'), 'S2', 8, 15, #{merchantCode,jdbcType=VARCHAR}, 0, '2019-09-05 20:43:59', '2019-09-02 20:13:12')");
        sqls.add("INSERT INTO `cashmama-admin-rest`.`cl_stage`(`stage_code`, `stage_name`, `min_overdue_days`, `max_overdue_days`, `merchant_code`, `deleted`, `gmt_modified`, `gmt_create`) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_M1'), 'M1', 16, 30, #{merchantCode,jdbcType=VARCHAR}, 0, '2019-09-05 20:44:01', '2019-09-02 20:13:12')");
        sqls.add("INSERT INTO `cashmama-admin-rest`.`cl_stage`(`stage_code`, `stage_name`, `min_overdue_days`, `max_overdue_days`, `merchant_code`, `deleted`, `gmt_modified`, `gmt_create`) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_M2'), 'M2', 31, 60, #{merchantCode,jdbcType=VARCHAR}, 0, '2019-09-05 20:44:03', '2019-09-02 20:13:12')");
        sqls.add("INSERT INTO `cashmama-admin-rest`.`cl_stage`(`stage_code`, `stage_name`, `min_overdue_days`, `max_overdue_days`, `merchant_code`, `deleted`, `gmt_modified`, `gmt_create`) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_M3'), 'M3', 61, NULL, #{merchantCode,jdbcType=VARCHAR}, 0, '2019-09-05 20:44:05', '2019-09-02 20:13:12')");
        sqls.forEach(sql -> {
            params.put("sql", sql);
            getSqlSession().selectList(getStatement("executeStatsSql"), params);
        });
    }

    @Override
    public List<Object> executeStatsSql(String sql) {
        Map<String, Object> params = new HashMap<>();
        params.put("sql", sql);
        return getSqlSession().selectList(getStatement("executeStatsSql"), params);
    }

    @Override
    public void createProductCategory(String productCategory, String merchantCode, String appName, String appCode) {
        Map<String, Object> params = new HashMap<>();
        params.put("productCategory", productCategory);
        params.put("merchantCode", merchantCode);
        params.put("appName", appName);
        params.put("appCode", appCode);

        List<String> sqls = new ArrayList<>();
        sqls.add("INSERT INTO `cashmama-product`.`product_category` (`code`, `name`, `money_floor`, `money_upper`, `is_selling`,`interest_strategy`, `payback_period_unit`, `payback_period_floor`,`payback_period_upper`, `money_step`, `payback_period_step`, `sort`,`other_config_data`, `is_deleted`,`merchant_code`) VALUES  (#{productCategory,jdbcType=VARCHAR}, #{productCategory,jdbcType=VARCHAR}, 500.00, 6000.00, 1, 'PAY_ONCE','DAY', 7, 14, 500.00, 0, 1,'{\"needContact\":false, \"autoJumper\":true,\"showQuatoButton\":false, \"redirectUrl\":\"user/app/url\" }',0000, #{merchantCode,jdbcType=VARCHAR})");

        sqls.add("INSERT INTO `cashmama-product`.`app_product_category` (`app_code`, `app_name`, `product_category`, `is_deleted`) VALUES (#{appCode,jdbcType=VARCHAR}, #{appName,jdbcType=VARCHAR}, #{productCategory,jdbcType=VARCHAR}, 0000)");

        sqls.forEach(sql -> {
            params.put("sql", sql);
            getSqlSession().selectList(getStatement("executeStatsSql"), params);
        });
    }

    @Override
    public void createMessageChannel(String merchantCode, Integer appId, String linkInfo) {
        Map<String, Object> params = new HashMap<>();
        params.put("merchantCode", merchantCode);
        params.put("appId", appId);
        params.put("linkInfo", linkInfo);
        getSqlSession().selectList(getStatement("createMessageChannel"), params);
    }

    @Override
    public void createMessageBizCode(String merchantCode, Integer appId) {
        Map<String, Object> params = new HashMap<>();
        params.put("merchantCode", merchantCode);
        params.put("appId", appId);

        List<String> sqls = new ArrayList<>();
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0001'), #{appId,jdbcType=INTEGER}, 'Credit Bazar login OPT', 1, null, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0002'), #{appId,jdbcType=INTEGER}, 'Credit Bazar login OPT', 1, null, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0003'), #{appId,jdbcType=INTEGER}, 'Credit Bazar login OPT', 1, null, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0004'), #{appId,jdbcType=INTEGER}, 'login with new device', 1, null, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0005'), #{appId,jdbcType=INTEGER}, 'Account thaw reminder', 0, null, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0006'), #{appId,jdbcType=INTEGER}, 'Account thaw reminder', 0, null, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0007'), #{appId,jdbcType=INTEGER}, 'Account thaw reminder', 1, null, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0008'), #{appId,jdbcType=INTEGER}, 'Account thaw reminder', 1, null, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0009'), #{appId,jdbcType=INTEGER}, 'Account thaw reminder', 1, null, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0010'), #{appId,jdbcType=INTEGER}, 'Account thaw reminder', 1, null, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0011'), #{appId,jdbcType=INTEGER}, 'Account thaw reminder', 1, null, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0012'), #{appId,jdbcType=INTEGER}, 'Account thaw reminder', 1, null, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0013'), #{appId,jdbcType=INTEGER}, 'Account thaw reminder', 1, null, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0014'), #{appId,jdbcType=INTEGER}, 'Account thaw reminder', 1, null, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0015'), #{appId,jdbcType=INTEGER}, 'Account thaw reminder', 1, null, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0016'), #{appId,jdbcType=INTEGER}, 'Account thaw reminder', 0, null, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0017'), #{appId,jdbcType=INTEGER}, 'Payment failed', 1, null, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0018'), #{appId,jdbcType=INTEGER}, 'Payment failed', 0, null, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0019'), #{appId,jdbcType=INTEGER}, 'Payment failed', 0, null, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0020'), #{appId,jdbcType=INTEGER}, 'Payment failed', 0, null, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0021'), #{appId,jdbcType=INTEGER}, 'Payment failed', 0, null, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0022'), #{appId,jdbcType=INTEGER}, 'Credit Bazar console login OPT', 1, null, 'system', '2017-10-19 20:17:29', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0023'), #{appId,jdbcType=INTEGER}, 'CREDIT_BAZAR_REEDIT', 1, null, 'system', '2017-10-19 20:17:29', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0024'), #{appId,jdbcType=INTEGER}, 'Loan Audit Rejection', 1, null, 'system', '2018-11-07 15:09:43', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0025'), #{appId,jdbcType=INTEGER}, 'Repayment reminder', 1, null, 'system', '2018-11-23 09:22:18', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0026'), #{appId,jdbcType=INTEGER}, 'User re-call', 1, null, 'system', '2019-01-25 08:43:16', 'system', '2019-04-15 09:02:27')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0027'), #{appId,jdbcType=INTEGER}, 'e-sign reminder', 1, null, 'system', '2019-05-28 07:27:20', 'system', '2019-05-28 07:27:20')");
        sqls.add("INSERT INTO `message_center`.biz_function (biz_code, app_id, biz_name, status, desc_info, create_person, create_time, update_person, update_time) VALUES (CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0029'), #{appId,jdbcType=INTEGER}, 'cashmama sign OPT', 1, null, 'system', '2017-10-09 15:30:54', 'system', '2017-10-21 03:46:50')");

        sqls.forEach(sql -> {
            params.put("sql", sql);
            getSqlSession().selectList(getStatement("executeStatsSql"), params);
        });
    }

    @Override
    public void createMessageTemplate(String merchantCode, Integer appId, String appName) {
        Map<String, Object> params = new HashMap<>();
        params.put("merchantCode", merchantCode);
        params.put("appId", appId);
        params.put("appName", appName);

        List<String> sqls = new ArrayList<>();
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0001'), 1, 1, 1, CONCAT('Your verification code for login at ',#{appName,jdbcType=VARCHAR},' app is: ##VERIFY_CODE##. Valid for 10 minutes.'), null, 'TEXTLOCAL', 'VERIFY_CODE', 'Credit Bazar login OPT', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-07-29 15:38:56', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0002'), 1, 1, 1, CONCAT('Your verification code is: ##VERIFY_CODE##, valid for 10 minutes. This verification code is only used for login of ',#{appName,jdbcType=VARCHAR},'. If it is not your operation, please ignore it.'), null, 'TEXTLOCAL', 'VERIFY_CODE', 'Credit Bazar login OPT', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-07-29 15:39:36', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0003'), 1, 1, 1, CONCAT('Your verification code is: ##VERIFY_CODE##, valid for 10 minutes. This verification code is only used for login of ',#{appName,jdbcType=VARCHAR},'. If it is not your operation, please ignore it.'), null, 'TEXTLOCAL', 'VERIFY_CODE', 'Credit Bazar login OPT', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-07-29 15:39:36', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0004'), 1, 1, 1, CONCAT('Your verification code for login in new device on ',#{appName,jdbcType=VARCHAR},' app is: ##VERIFY_CODE##, valid for 10 minutes.'), null, 'TEXTLOCAL', 'VERIFY_CODE', 'login with new device', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-07-29 15:39:36', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0005'), 1, 1, 1, CONCAT('(Account Thaw reminder) Dear ##name##, your account has been re-activated, please login to ',#{appName,jdbcType=VARCHAR},' App to apply for a loan, up to Rs. 5000!'), null, 'TEXTLOCAL', 'name', 'Account thaw reminder', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-07-29 15:39:36', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0006'), 1, 1, 1, CONCAT('(Account Thaw reminder) Dear ##name##, your account has been re-activated, please login to ',#{appName,jdbcType=VARCHAR},' App to apply for a loan, up to Rs. 5000!'), null, 'TEXTLOCAL', 'name', 'Account thaw reminder', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-07-29 15:39:36', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0007'), 1, 1, 1, CONCAT('Dear ##name##, The Loan application for amount ##totalAmount## has been approved. Please Esign in ',#{appName,jdbcType=VARCHAR},' APP and we will transfer it to your account, it may take 24-48hrs on bank working days, Thank you for using ',#{appName,jdbcType=VARCHAR},'.'), null, 'TEXTLOCAL', 'name,totalAmount', 'Approval passed', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-07-29 15:39:36', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0008'), 1, 1, 1, CONCAT('Dear ##name##, your loan application has not passed the review, please login to the app again on ##date##.'), null, 'TEXTLOCAL', 'name,date', 'Approval reject', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:28', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0009'), 1, 1, 1, CONCAT('Dear ##name##, your loan application has not passed the review, please login to the app again on ##date##.'), null, 'TEXTLOCAL', 'name,date', 'Approval reject', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:28', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0010'), 1, 1, 1, CONCAT('(Receipt reminder) Dear ##name##, The loan has been disbursed to your bank with account number [##bankNo##], repay on time to avoid late payment charges.'), null, 'TEXTLOCAL', 'name,bankNo', 'Receipt reminder', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:28', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0011'), 1, 1, 1, CONCAT('(Receipt reminder) Dear ##name##, The loan has been disbursed to your bank with account number [##bankNo##], repay on time to avoid late payment charges.'), null, 'TEXTLOCAL', 'name,bankNo', 'Payment failed', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:28', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0012'), 1, 1, 1, CONCAT('(Repayment reminder) Dear ##name##, Your loan of amount Rs. ##totalAmount## is due on ##date##, repay on time to avoid late payment charges.'), null, 'TEXTLOCAL', 'name,totalAmount,date', 'Repayment reminder', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:28', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0013'), 1, 1, 1, CONCAT(''), null, 'TEXTLOCAL', 'totalAmount,date,amount', 'Repayment reminder', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:28', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0014'), 1, 1, 1, CONCAT('(Expiration reminder) Dear ##name##, Your Rs. ##totalAmount## loan is due today, please repay in time to avoid overdue penalty.'), null, 'TEXTLOCAL', 'name,totalAmount', 'Expiration reminder', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:28', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0015'), 1, 1, 1, CONCAT(''), null, 'TEXTLOCAL', '', 'Expiration reminder', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:28', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0016'), 1, 1, 1, CONCAT('(Successful repayment) Dear ##name##, we have successfully received payment of Rs. ##totalAmount##. Please wait for 24 hrs to reflect the amount in your loan.'), null, 'TEXTLOCAL', 'name,totalAmount', 'Successful repayment', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:28', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0017'), 1, 1, 1, CONCAT(''), null, 'TEXTLOCAL', 'bankNo,amount', '代扣成功', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:28', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0018'), 1, 1, 1, CONCAT(''), null, 'TEXTLOCAL', 'bankNo,time', '代扣失败（第一次）', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:28', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0019'), 1, 1, 1, CONCAT(''), null, 'TEXTLOCAL', 'bankNo', '代扣失败（第二次）', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:28', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0020'), 1, 1, 1, CONCAT('(Overdue reminder) Dear ##name##, Your loan of Rs. ##amount## was due on ##overdueTime##. It is currently overdue for ##overdueDay## day. Please repay in time to avoid overdue penalty.'), null, 'TEXTLOCAL', 'name,amount,overdueTime,overdueDay', 'Overdue reminder', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:28', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0021'), 1, 1, 1, CONCAT('(Overdue reminder) Dear ##name##, Your loan of Rs. ##amount## was due on ##overdueTime##. It is currently overdue for ##overdueDay## day. Please repay in time to avoid overdue penalty.'), null, 'TEXTLOCAL', 'name,amount,overdueTime,overdueDay', 'Overdue reminder', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-04-15 09:02:28', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0022'), 1, 1, 1, CONCAT('Your verification code is: ##VERIFY_CODE##, valid for 10 minutes. This verification code is only used for login of ',#{appName,jdbcType=VARCHAR},'. If it is not your operation, please ignore it.'), null, 'TEXTLOCAL', 'VERIFY_CODE', 'Credit Bazar console login OPT', 1, 'system', '2017-10-19 20:17:29', 'system', '2019-07-29 15:39:36', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0023'), 1, 1, 1, CONCAT('Some information  you edit  is invalid. Please click to re-edit for approval.'), null, 'TXTLCL', null, 'Re-edit information', 1, 'system', '2017-10-19 20:17:29', 'system', '2019-04-15 09:02:28', null, '')");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0024'), 1, 1, 1, CONCAT('Dear ##name##, your application was rejected because your e-sign name does not match KYC information.You can apply again on ##date##.'), null, 'TXTLCL', 'name,date', 'Loan Audit Rejection', 1, 'system', '2018-11-07 15:07:39', 'system', '2019-04-15 09:02:28', null, null)");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0025'), 1, 1, 1, CONCAT('Dear ##userName##,This is warm reminder that ##days## later is your bill day . Please make repayment in time to avoid late payment fees and affect your credit.'), null, '', 'userName,days', 'Repayment remider', 1, 'system', '2018-11-23 09:21:16', 'system', '2019-04-15 09:02:28', null, null)");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0026'), 1, 1, 1, CONCAT('Dear ',#{appName,jdbcType=VARCHAR},' user, as a previleged user, we ask you to avail the loan facility again for your financial needs. Apply now for your next loan!'), null, '', null, 'user re-call', 1, 'system', '2019-01-25 08:41:10', 'system', '2019-07-29 15:39:36', null, null)");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0027'), 1, 1, 1, CONCAT('Hello ##name##, we have approved your loan and it''s waiting to be disbursed. We need you to e-Sign the loan agreement. If you have not e-Signed after 7 days of approval, your loan application will be void.'), null, '', 'name', 'e-sign reminder', 1, 'system', '2019-05-28 07:29:14', 'system', '2019-05-28 07:38:16', null, null)");
        sqls.add("INSERT INTO `message_center`.message_template (app_id, biz_code, is_common, msg_type, template_type, content, third_tmp_id, servicer_code, replace_list, desc_info, status, create_person, create_time, update_person, update_time, type, sign) VALUES (#{appId,jdbcType=INTEGER},CONCAT(#{merchantCode,jdbcType=VARCHAR},'_0029'), 1, 1, 1, CONCAT('##VERIFY_CODE## is your OTP for signing loan application with ',#{appName,jdbcType=VARCHAR},', OTP valid for 30 mins only.'), null, 'TEXTLOCAL', 'VERIFY_CODE', 'cashmama sign OPT', 1, 'system', '2017-10-09 15:30:54', 'system', '2019-07-29 15:39:36', null, '')");

        sqls.forEach(sql -> {
            params.put("sql", sql);
            getSqlSession().selectList(getStatement("executeStatsSql"), params);
        });
    }

    @Override
    public MsgCommonVo getServicerIdByChannelNameAndAppId(String channelName, Integer appId) {
        Map<String, Object> params = new HashMap<>();
        params.put("channelName", channelName);
        params.put("appId", appId);
        return getSqlSession().selectOne(getStatement("getServicerIdByChannelNameAndAppId"), params);
    }

    @Override
    public MsgCommonVo getFunctionIdByBizCodeAndAppId(String bizCode, Integer appId) {
        Map<String, Object> params = new HashMap<>();
        params.put("bizCode", bizCode);
        params.put("appId", appId);
        return getSqlSession().selectOne(getStatement("getFunctionIdByBizCodeAndAppId"), params);
    }

    @Override
    public MsgCommonVo getTemplateIdByBizCodeAndAppId(String bizCode, Integer appId) {
        Map<String, Object> params = new HashMap<>();
        params.put("bizCode", bizCode);
        params.put("appId", appId);
        return getSqlSession().selectOne(getStatement("getTemplateIdByBizCodeAndAppId"), params);
    }

    @Override
    public void createBusinessRef(Integer appId, Integer servicerId, Integer functionId, Integer templateId) {

        Map<String, Object> params = new HashMap<>();
        params.put("appId", appId);
        params.put("servicerId", servicerId);
        params.put("functionId", functionId);
        params.put("templateId", templateId);
        getSqlSession().selectList(getStatement("createBusinessRef"), params);
    }
}